/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cn.jgls.bi.dataprocess;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.util.Iterator;

/**
 *
 * @author Administrator
 */
public class pcaDataJdbcDao extends JdbcDaoSupport implements pcaDataDao {
    //客品数处理部分函数
    public void pcaData(Date adTsnDate,int aiShopId,String asShopBm,
        int aiDateNo,String asYear,int aiYybh,int aiMan_id,String asTname){
        //开始处理
        int TsnAmt=0;
        int iRc;
        int aiYear;
        int TsnSort;
        int iWeekAmt=0,iYearAmt=0,iWeekAmt2=0,iYearAmt2=0;
        String sql="";
        float fWeekAddup=0,fYearAddup=0,fWeekAddup2=0,fYearAddup2=0;
        List lstWeek,lstYear,lstPca,lstPcaAmt;
        float fPca=0,fPcaTotal;
        int[] tsn = {0,0,0,0,0,0};
        System.out.println("now processing    PCA's Data... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        aiYear = Integer.parseInt(asYear);
        JdbcTemplate jt = getJdbcTemplate();
        sql = "select avg(count(lb)) as pcavalue  from " + asTname +
                    " where saledate = ? and bm=? and trnno<>9999 group by jqh,empl_id,trnno";
        Object[] params = new Object[]{adTsnDate, asShopBm};
        int[] types = {Types.DATE, Types.VARCHAR};
        lstPca=jt.query(sql,params,types,new RowMapperResultReader(new PcaMxRM()));
        Iterator Iter = lstPca.iterator(); 
        PcaMx ltam = new PcaMx();
        while(Iter.hasNext()){
            ltam = (PcaMx)Iter.next();
            fPca  = ltam.getPca_value();
        }
        
        if (fPca <= 2) {
            TsnSort = 1;
        } else if (fPca > 2 && fPca <= 2.5) {
            TsnSort = 2;
        } else if (fPca > 2.5 && fPca <= 3) {
            TsnSort = 3;
        } else if (fPca > 3 && fPca <= 3.5) {
            TsnSort = 4;
        } else if (fPca > 3.5 && fPca <= 4) {
            TsnSort = 5;
        } else {
            TsnSort = 6;
        }
        
        
        //取周平均累计
        sql="select week_addup,week_amt from t_bi_pca_mx where tsn_date = " +
                "(select max(tsn_date) from t_bi_pca_mx where tsn_date < ? " +
                "and tsn_date_no=? and tsn_id=?)" +
                " and tsn_id = ? and tsn_year = ?";
        params = new Object[]{adTsnDate,aiDateNo,aiShopId,aiShopId,aiYear};
        types = new int[]{Types.DATE,Types.INTEGER,Types.INTEGER,Types.INTEGER,Types.INTEGER};
        lstWeek=jt.query(sql,params,types,new RowMapperResultReader(new PcaMxWeekRM()));
        Iter = lstWeek.iterator(); 
        ltam = new PcaMx();
        while(Iter.hasNext()){
            ltam = (PcaMx)Iter.next();
            iWeekAmt2  = ltam.getWeek_amt();
            fWeekAddup2 = ltam.getWeek_addup();
        }
        
        //取年平均累计
        sql="select year_addup,year_amt from t_bi_pca_mx where tsn_date = (? - 1) and tsn_id = ? and tsn_year = ?";
        params = new Object[]{adTsnDate,aiShopId,aiYear};
        types = new int[]{Types.DATE,Types.INTEGER,Types.INTEGER};
        lstYear=jt.query(sql,params,types,new RowMapperResultReader(new PcaMxYearRM()));
        Iter = lstYear.iterator(); 
        ltam = new PcaMx();
        while(Iter.hasNext()){
            ltam = (PcaMx)Iter.next();
            iYearAmt2  = ltam.getYear_amt();
            fYearAddup = ltam.getYear_addup();
        }

        iWeekAmt   = iWeekAmt2 + 1;
        fWeekAddup = fWeekAddup2 + fPca;
        iYearAmt   = iYearAmt2 + 1;
        fYearAddup = fYearAddup + fPca; 
        
        //begin insert into TsnAmt
        //1:delete
        params = new Object[]{adTsnDate, aiShopId};
        types = new int[]{Types.DATE, Types.INTEGER};
        sql = "delete from t_bi_pca_mx where tsn_date = ? and tsn_id = ?";
        iRc = jt.update(sql, params, types);
        //2:insert
        sql = "insert into t_bi_pca_mx " +
                "(tsn_date,tsn_id,pca_value,tsn_year,tsn_date_no,yybh,man_id,tsn_sort" +
                ",week_addup,week_amt,year_addup,year_amt)" +
                " values(?,?,?,?,?,?,?,?,?,?,?,?)";
        params = new Object[]{adTsnDate, aiShopId, fPca, 
        aiYear, aiDateNo, aiYybh, aiMan_id, TsnSort,fWeekAddup,iWeekAmt,fYearAddup,iYearAmt};
        types = new int[]{Types.DATE, Types.INTEGER, Types.FLOAT,
        Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER
        ,Types.FLOAT,Types.INTEGER,Types.FLOAT,Types.INTEGER};
        iRc = jt.update(sql, params, types);
        //插入交易笔数
        sql="select count(lb) as tsnamt  from"+asTname+
        "where saledate = ? and bm=? and trnno<>9999 group by jqh,empl_id,trnno ";
        params = new Object[]{adTsnDate, asShopBm};
        types  = new int[] {Types.DATE, Types.VARCHAR};
        lstPcaAmt=jt.query(sql,params,types,new RowMapperResultReader(new PcaTsnAmtRM()));
        Iter = lstPcaAmt.iterator(); 
        PcaTsnAmt ltam2 = new PcaTsnAmt();
        while(Iter.hasNext()){
            ltam2 = (PcaTsnAmt)Iter.next();
            TsnAmt  = ltam2.getTsn_amt();
            if (TsnAmt <= 2) {
                TsnSort = 1;
            } else if (TsnAmt > 2 && TsnAmt <= 4) {
                TsnSort = 2;
            } else if (TsnAmt > 4 && TsnAmt <= 6) {
                TsnSort = 3;
            } else if (TsnAmt > 6 && TsnAmt <= 8) {
                TsnSort = 4;
            } else if (TsnAmt > 8 && TsnAmt <= 10) {
                TsnSort = 5;
            } else {
                TsnSort = 6;
            }
            tsn[TsnSort - 1] = tsn[TsnSort - 1] + 1;
        }
        //insert into db
        for(int i=0;i<6;i++){
            sql = "insert into t_bi_pca_tsnamt " +
                    "(tsn_date,tsn_id,tsn_amt,yybh,man_id,tsn_sort)" +
                    " values(?,?,?,?,?,?)";
            params = new Object[]{adTsnDate, aiShopId,tsn[i] , 
             aiYybh, aiMan_id, i+1 };
            types = new int[]{Types.DATE, Types.INTEGER, Types.INTEGER, Types.INTEGER, 
            Types.INTEGER, Types.INTEGER};
            iRc = jt.update(sql, params, types);
        }
    
    }
   class PcaMxYearRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            PcaMx wavg = new PcaMx();
            wavg.setYear_addup(new Float(rs.getFloat("year_addup")));
            wavg.setYear_amt(new Integer(rs.getInt("year_amt")));
            return wavg;
        }
    }
   class PcaMxWeekRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            PcaMx wavg = new PcaMx();
            wavg.setWeek_addup(new Float(rs.getFloat("week_addup")));
            wavg.setWeek_amt(new Integer(rs.getInt("week_amt")));
            return wavg;
        }
    }
   class PcaMxRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            PcaMx wavg = new PcaMx();
            wavg.setPca_value(rs.getFloat("pcavalue"));
            return wavg;
        }
    }
   class PcaTsnAmtRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            PcaTsnAmt wavg = new PcaTsnAmt();
            wavg.setTsn_amt(new Integer(rs.getInt("tsnamt")));
            return wavg;
        }
    }
     

}
